package com.bs.areport.service.report;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jaxen.function.StringFunction;


import com.bs.areport.service.util.DataUtil;
import com.bs.areport.service.util.EntityType;
import com.bs.areport.service.util.MethodUtil;
import com.bs.areport.sys.Tools;
import com.bs.restframework.context.util.SystemContextUtil;
import com.bs.restframework.db.Database;
/**
 * 
 * <p>类名称：收费项目月情况表服务类</p>
 * <p>创建时间：2012-03-08</p>
 * <p>作者：李新宇</p>
 */
public class ChargeIncomeByMonthDao {
	/**
	 * 
	 * <p>函数名称：getReport</p>
	 * <p>功能说明：获取报表数据</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ;/p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public Map<String, Object> getReport(Map<String, Object> valueMap,String userDeptCode) throws Exception
    {
		Database db = SystemContextUtil.getDatabase(this);
		Map<String, Object> resultMap=new HashMap<String, Object>();
		String condition="";
		//级长
		String chargeJc=MethodUtil.getCharghJC(db);
		//获取参数
		int page = Integer.valueOf((String)valueMap.get("page"));
		int rows = Integer.valueOf((String)valueMap.get("rows"));
		String chargeCode=(String)valueMap.get("chargeCode");

		//陈金滨  2012-03-23   增加查询条件 
		String year= (String) valueMap.get("year");
		
		if (year==null||"".equals(year)){
			SimpleDateFormat   sDateFormat   =   new   SimpleDateFormat("yyyy");   
			year   =  sDateFormat.format(new   java.util.Date());
		}
		
		String isQuery=(String)valueMap.get("isQuery");
		Integer chargeLevel=1;
		if(valueMap.get("chargeLevel")!=null&&!"undefined".equals(valueMap.get("chargeLevel"))&&Integer.valueOf((String)valueMap.get("chargeLevel"))>0)
		    chargeLevel=Integer.valueOf((String)valueMap.get("chargeLevel"));
		
		if(isQuery!=null&&!"".equals(isQuery)&&chargeCode!=null&&!"".equals(chargeCode))
			chargeLevel=DataUtil.getLevel(chargeJc, chargeCode);
		
		//计算查询编码的长度
		String chargeCodeLenght=getCodeLenght(chargeJc, chargeLevel);

		String sql ="SELECT  substr(C.F_CODE,0,"+chargeCodeLenght+") AS CHARGECODE,R.F_AMT AS AMT,R.F_MON AS MON ";
		sql+=" FROM "+ EntityType.ARP_BUS_CHRGMON_TABLE+" R";
		sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" C ON C.F_ID=R.F_CHRGID ";
		if(userDeptCode!=null&&!"".equals(userDeptCode))
			condition+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=R.F_DEPTID";
		condition+=" WHERE 1=1 AND R.F_MON LIKE '"+year+"%'" ;
		if(chargeCode==null||"".equals(chargeCode))	
			condition+=" AND C.F_LEVEL>=1";
		else
			condition+=" AND C.F_CODE LIKE '"+chargeCode+"%' AND C.F_LEVEL>="+String.valueOf((chargeLevel));
		if(userDeptCode!=null&&!"".equals(userDeptCode))
			condition+=" AND D.F_CODE LIKE '"+userDeptCode+"%'";
		
		sql+=condition;
		//生产报表所需的sql，行列转置
		sql=getSQL(sql); 
		//生成分页sql
		sql = Tools.formatPagingSQL(sql,rows,page);

		
		List<Map> listMap = db.queryList(Map.class, sql,null);
		
		int count = getUserListCount(db,condition,chargeCodeLenght);
		
		//合计
		List<String> colList= new ArrayList<String>();
			colList.add("JANUARY");
			colList.add("FEBRUARY");
			colList.add("MARCH");
			colList.add("APRIL");
			colList.add("MAY");
			colList.add("JUNE");
			colList.add("JULY");
			colList.add("AUGUST");
			colList.add("SEPTEMBER");
			colList.add("OCTOBER");
			colList.add("NOVEMBER");
			colList.add("DECEMBER");
			Map<String, Object> footer=new HashMap<String, Object>();
			if(listMap!=null&&listMap.size()>0){
			
			footer.put("CHARGENAME", "合计");
			for (Map map : listMap) {
				for (String m : colList) {
					if(footer.get(m)==null)
						footer.put(m, new Double(0));
					Double fDouble=(Double)footer.get(m);
					Double sDouble=Double.parseDouble(map.get(m).toString());
					Double sumDouble=fDouble+sDouble;
					footer.put(m,sumDouble );
				}
			}
		}else {
			footer.put("CHARGENAME", "合计");
			for (String m : colList) {
				footer.put(m, new Double(0));
			}
		}
		List<Map> foots=new ArrayList<Map>();
		foots.add(footer);
		resultMap.put("footer",foots);
		//返回结果
		resultMap.put("rows", listMap);
		resultMap.put("total", count);
		resultMap.put("chargeCode", chargeCode);
		resultMap.put("chargeLevel", chargeLevel);
		resultMap.put("year", year);
		return resultMap;
	}
	
	/**
	 * 
	 * <p>函数名称：getColString</p>
	 * <p>功能说明：根据要查询的项目，生成查询列</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static String getCodeLenght(String jc,Integer level){
        Integer len=0;
		
        for(int i=0;i<level;i++){
        	len=len+Integer.parseInt(jc.substring(i, i+1));
        }
        
		return String.valueOf(len);
	}
	
	/**
	 * 
	 * <p>函数名称：getColString</p>
	 * <p>功能说明：根据要查询的项目，生成查询列</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static String getSQL(String sqlString){
		String colString="";
		String sql="";

		colString+=",sum(decode(substr(MON,5,2),'01', AMT,0)) JANUARY";
		colString+=",sum(decode(substr(MON,5,2),'02', AMT,0)) FEBRUARY";
		colString+=",sum(decode(substr(MON,5,2),'03', AMT,0)) MARCH";
		colString+=",sum(decode(substr(MON,5,2),'04', AMT,0)) APRIL";
		colString+=",sum(decode(substr(MON,5,2),'05', AMT,0)) MAY";
		colString+=",sum(decode(substr(MON,5,2),'06', AMT,0)) JUNE";
		colString+=",sum(decode(substr(MON,5,2),'07', AMT,0)) JULY";
		colString+=",sum(decode(substr(MON,5,2),'08', AMT,0)) AUGUST";
		colString+=",sum(decode(substr(MON,5,2),'09', AMT,0)) SEPTEMBER";
		colString+=",sum(decode(substr(MON,5,2),'10', AMT,0)) OCTOBER";
		colString+=",sum(decode(substr(MON,5,2),'11', AMT,0)) NOVEMBER";
		colString+=",sum(decode(substr(MON,5,2),'12', AMT,0)) DECEMBER";

		sql="SELECT Z.CHARGECODE,M.F_NAME AS CHARGENAME,M.F_ISLEAF AS CHRGELEAF  "+colString+" FROM (";
		sql+=sqlString;
		sql+=") Z LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" M ON M.F_CODE=Z.CHARGECODE ";
		sql+=" GROUP BY Z.CHARGECODE,M.F_NAME,M.F_ISLEAF";
		return sql;
	}
	
	/**
	 * 
	 * <p>函数名称：getUserListCount</p>
	 * <p>功能说明：获取表记录数</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static int getUserListCount(Database db,String condition,String chrgLen)throws Exception {
        int count=0;
		String sql = "SELECT Z.CHARGECODE FROM ( ";
		sql+=" SELECT substr(C.F_CODE, 0, "+chrgLen+") AS CHARGECODE FROM "+EntityType.ARP_BUS_CHRGMON_TABLE+" R ";
        sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" C ON C.F_ID = R.F_CHRGID ";
		sql+=condition;
        sql+=") Z  GROUP BY Z.CHARGECODE";
		List<Map> list =db.queryList(Map.class, sql,null);

		if(list!=null)
			count=list.size();

		return count;
	}
	/**
	 * 
	 * <p>函数名称：getChargeTree</p>
	 * <p>功能说明：获取项目树</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-22</p>
	 * <p>作者：陈金滨</p>
	 */
	public List<Map> getChargeTree()throws Exception 
	{
		Database db = SystemContextUtil.getDatabase(this);
		String sql = "SELECT F_ID AS \"id\", '[' || F_CODE || ']' || F_NAME AS \"text\" FROM "+EntityType.ARP_BSC_CHARGE_TABLE;
		List<Map>  list=db.queryList(Map.class, sql, null);		
		
		return list;
	}	
	/**
	 * 
	 * <p>函数名称：getYear</p>
	 * <p>功能说明：获取年份</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-22</p>
	 * <p>作者：陈金滨</p>
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public Map<String,Object> getYear() throws Exception 
	{
		Database db = SystemContextUtil.getDatabase(this);
		String sql = "SELECT MIN(F_MON) AS year FROM "+EntityType.ARP_BUS_CHRGMON_TABLE;
		Map<String, Object> vMap= new HashMap<String, Object>();
		vMap=db.queryObject(Map.class, sql, null);	
	
		return vMap;
	}	
	
	@SuppressWarnings("unchecked")
	public List<Map> ResultYear()throws Exception 
	{
		Database db = SystemContextUtil.getDatabase(this);
		String sql = "SELECT  distinct substr(F_Mon,0,4) AS \"id\",substr(F_Mon,0,4) AS \"text\" FROM "+EntityType.ARP_BUS_CHRGMON_TABLE;
		List<Map>  list=db.queryList(Map.class, sql, null);	
		return list;
	}	
	
	/**
	 * 
	 * <p>函数名称：getQtip</p>
	 * <p>功能说明：获取图表数据/p>
	 * <p>参数说明：</p>
	 * <p>创建时间：2012-03-13</p>
	 * <p>作者：李新宇</p>
	 */
	public List<Map> getQtip(Map<String, Object> valueMap,String userDeptCode) throws Exception
	{
		Database db = SystemContextUtil.getDatabase(this);
		
		//级长
		String chargeJc=MethodUtil.getCharghJC(db);
		String month=(String)valueMap.get("month");
		String year=(String)valueMap.get("year");
		String chargeCode=(String)valueMap.get("chargeCode");
		Integer chargeLevel=1;
		if(valueMap.get("chargeLevel")!=null&&Integer.valueOf((String)valueMap.get("chargeLevel"))>0)
		    chargeLevel=Integer.valueOf((String)valueMap.get("chargeLevel"));
		String groupType=(String)valueMap.get("groupType");
		
		String sql="";
				
		//以项目作为百分比
		if("month".equals(groupType))
		{
			String chargeCodeLenght=getCodeLenght(chargeJc,chargeLevel);
			sql="SELECT Z.CODE, M.F_NAME AS \"label\", SUM(Z.AMT) AS \"value\" FROM (";
			sql+="SELECT substr(O.F_CODE,0,"+chargeCodeLenght+") AS CODE,R.F_AMT AS AMT";
			sql+=" FROM "+ EntityType.ARP_BUS_CHRGMON_TABLE+" R";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" O ON O.F_ID=R.F_CHRGID ";
			if(userDeptCode!=null&&!"".equals(userDeptCode))
				sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=R.F_DEPTID";
			sql+=" WHERE O.F_LEVEL>='"+chargeLevel+"' AND R.F_MON LIKE '"+year+"%'";
			if(month!=null&&!"".equals(month)){
				sql+=" AND R.F_MON = '"+year+month+"'";			
			}
			if(chargeCode!=null&&!"".equals(chargeCode)&&!"null".equals(chargeCode)){
				sql+=" AND O.F_CODE LIKE '"+chargeCode+"%'";
			}
			if(userDeptCode!=null&&!"".equals(userDeptCode))
				sql+=" AND D.F_CODE LIKE '"+userDeptCode+"%'";
			sql+=") Z ";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" M ON M.F_CODE=Z.CODE ";
			sql+=" GROUP BY M.F_NAME,Z.CODE ORDER BY to_number(Z.CODE)";
			
		}else if("charge".equals(groupType))//以月份作为百分比
		{	
			String chargeCodeLenght=getCodeLenght(chargeJc,chargeLevel);
			sql="SELECT Z.F_NAME AS \"label\", SUM(Z.F_AMT) AS \"value\" FROM (";
			sql+="SELECT (CASE WHEN substr(R.F_MON,5,2)='01' THEN '1月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='02' THEN '2月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='03' THEN '3月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='04' THEN '4月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='05' THEN '5月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='06' THEN '6月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='07' THEN '7月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='08' THEN '8月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='09' THEN '9月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='10' THEN '10月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='11' THEN '11月' ";
			sql+=" WHEN substr(R.F_MON,5,2)='12' THEN '12月' ";
			sql+=" END) AS F_NAME,R.F_AMT ";
			sql+=" FROM "+ EntityType.ARP_BUS_CHRGMON_TABLE+" R";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" O ON O.F_ID=R.F_CHRGID ";
			if(userDeptCode!=null&&!"".equals(userDeptCode))
				sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=R.F_DEPTID";
			sql+=" WHERE O.F_LEVEL>='"+chargeLevel+"' AND R.F_MON LIKE '"+year+"%'";
			if(month!=null&&!"".equals(month)){
				sql+=" AND R.F_MON = '"+year+month+"'";			
			}
			if(chargeCode!=null&&!"".equals(chargeCode)&&!"null".equals(chargeCode)){ 
				sql+=" AND O.F_CODE LIKE '"+chargeCode+"%'";
			}
			if(userDeptCode!=null&&!"".equals(userDeptCode))
				sql+=" AND D.F_CODE LIKE '"+userDeptCode+"%'";
			sql+=") Z Group by Z.F_NAME ORDER BY to_number( substr(Z.F_NAME,1,(LENGTH(Z.F_NAME)-1)))";
		}
		
		List<Map> list=db.queryList(Map.class, sql, null);
		
		return list;
	}
}
